package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Locale;
import java.util.Map;

import object.HistoryParcel;
import object.ResetBox;
import object.UnTakenParcel;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import POJO.Courier;
import POJO.Parcel;
import POJO.Terminal;
import POJO.User;

@Repository
public class ParcelDao {
	@Autowired
	JdbcTemplate jdbcTemplate;

	ArrayList<Parcel> parcels;
	ArrayList<UnTakenParcel> unTakenParcels;
	ArrayList<HistoryParcel> historyParcels;
	Parcel resultParcel;

	/**
	 * 查询本箱格包裹
	 * @param terminalID
	 * @param boxNum
	 * @param status
	 * @return
	 * by zgy
	 */
	public Parcel queryParcel(int terminalID,int boxNum,int status) {
		String sql = "SELECT * FROM package p where p.package_terminal_id=? and p.package_box_id=? and p.package_status=? order by p.package_store_time desc limit 1;";
		Object[] params = new Object[] { terminalID,boxNum,status };
		resultParcel = new Parcel();
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setBoxSeq(String.valueOf(rs.getInt("package_box_id")));
				resultParcel.setCheckCode(rs.getString("package_checkCode"));
				resultParcel.setParcelNum(rs.getString("package_parcel_id"));
				resultParcel.setPhoneNum(rs.getString("package_user_mobile_num"));
				resultParcel.setGetTime(rs.getTimestamp("package_get_time"));
				resultParcel.setStatus(rs.getInt("package_status"));
				resultParcel.setBelong(rs.getString("package_belong"));
			}
		});
		if (resultParcel.getId() == 0) {
			return null;
		} else {
			return resultParcel;
		}
	}
	
	/**
	 * 写入包裹异常日志
	 * @param parcel
	 * @param operate
	 * @param courier
	 * @param comment
	 * @return
	 * by zgy
	 */
	public int saveAbnormalParcelLog(final Parcel parcel,final String doorStatus,final String goodsStatus) {
		final String sql = "INSERT INTO log_parcel_abnormal "
				+ "(`terminalID`, `parcelNum`, `userPhone`, `boxNum` ,`doorStatus`,`goodsStatus`,`time`)"
				+ " VALUES (?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, parcel.getTerminalId());
				ps.setString(2, parcel.getParcelNum());
				ps.setString(3, parcel.getPhoneNum());
				ps.setString(4, parcel.getBoxSeq());
				ps.setString(5, doorStatus);
				ps.setString(6, goodsStatus);
				ps.setTimestamp(7, new Timestamp(new Date().getTime()));
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}
	
	/**
	 * 写入用户扣费日志
	 * @param parcel
	 * @param terminal
	 * @param costTotal
	 * @param costWay
	 * @return
	 */
	public int saveUserCostLog(final Parcel parcel,final Terminal terminal,final String costTotal,final String costWay) {
		final String sql = "INSERT INTO log_usercost "
				+ "(`packageID`, `userPhone`, `terminalID`, `costTotal` ,`costWay`)"
				+ " VALUES (?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, parcel.getId());
				ps.setString(2, parcel.getPhoneNum());
				ps.setInt(3, terminal.getTerminalID());
				ps.setString(4, costTotal);
				ps.setString(5, costWay);
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}
	
	public int save(final Parcel parcel, final Terminal terminal,
			final Courier courier) {
		final String saveParcelInfoSQL = "INSERT INTO package "
				+ "(`package_terminal_id`, `package_user_mobile_num`, `package_parcel_id`, `package_courier_id`, `package_box_id`, `package_status`, `package_belong`,`package_checkCode`)"
				+ " VALUES (?,?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(saveParcelInfoSQL,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, terminal.getTerminalID());
				ps.setString(2, parcel.getPhoneNum());
				ps.setString(3, parcel.getParcelNum());
				ps.setInt(4, courier.getCourierID());
				ps.setString(5, parcel.getBoxSeq());
				ps.setInt(6, 0);
				ps.setString(7, courier.getCourier_company());
				ps.setString(8, parcel.getCheckCode());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}
	/**
	 * 写入包裹日志
	 * @param parcel
	 * @param operate
	 * @param courier
	 * @param comment
	 * @return
	 * by zgy
	 */
	public int saveParcelLog(final Parcel parcel, final String operate,
			final Courier courier,final String comment) {
		final String sql = "INSERT INTO log_parcel "
				+ "(`log_parcel_parcelID`, `log_parcel_time`, `log_parcel_operate`, `log_parcel_courierID` ,`log_parcel_comment`)"
				+ " VALUES (?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, parcel.getId());
				ps.setTimestamp(2, new Timestamp(new Date().getTime()));
				ps.setString(3, operate);
				ps.setInt(4, courier.getCourierID());
				ps.setString(5, comment);
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	public String isExistParcelByPhoneNum(Terminal terminal, String phoneNum) {
		final Parcel parcel = new Parcel();
		String isExistParcelByPhoneNumSql = "SELECT package_checkCode FROM package "
				+ "WHERE package_status=0 AND package_terminal_id=? AND package_user_mobile_num=?;";
		Object[] params = new Object[] { terminal.getTerminalID(), phoneNum };
		jdbcTemplate.query(isExistParcelByPhoneNumSql, params,
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						parcel.setCheckCode(rs.getString("package_checkCode"));
					}
				});

		return parcel.getCheckCode();
	}

	public Parcel isItSameParcel(Terminal terminal, Parcel parcel) {
		String sql = "SELECT p.package_checkCode,p.package_parcel_id,p.package_user_mobile_num,p.package_id,p.package_terminal_id,p.package_box_id FROM package p where package_user_mobile_num=?"
				+ " and package_parcel_id=?"
				+ " and package_status=?"
				+ " and package_terminal_id=?";
		Object[] params = new Object[] { parcel.getPhoneNum(),
				parcel.getParcelNum(), 0, terminal.getTerminalID() };
		resultParcel = new Parcel();
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setBoxSeq(String.valueOf(rs
						.getInt("package_box_id")));
				resultParcel.setCheckCode(rs.getString("package_checkCode"));
				resultParcel.setParcelNum(rs.getString("package_parcel_id"));
				resultParcel.setPhoneNum(rs
						.getString("package_user_mobile_num"));
			}
		});
		if (resultParcel.getId() == 0) {
			return null;
		} else {
			return resultParcel;
		}
	}
	
	public ArrayList<ResetBox> getResetBoxes(int terminalID, int flag) {
		String sql = "SELECT p.package_checkCode,p.package_parcel_id,p.package_user_mobile_num,p.package_box_id FROM package p where p.package_flag=?"
				+ " and p.package_terminal_id=?";
		Object[] params = new Object[] {flag,terminalID};
		final ArrayList<ResetBox> resetBoxs = new ArrayList<ResetBox>();
		try {
			jdbcTemplate.query(sql, params, new RowCallbackHandler() {
				@Override
				public void processRow(ResultSet rs) throws SQLException {
					// TODO Auto-generated method stub
					ResetBox resetBox = new ResetBox();
					resetBox.setBoxSeq(String.valueOf(rs.getInt("package_box_id")));
					resetBox.setItemNum(rs.getString("package_parcel_id"));
					resetBox.setMobileNum(rs.getString("package_user_mobile_num"));
					resetBox.setOpenCode(rs.getString("package_checkCode"));
					resetBox.setType("1");
					resetBoxs.add(resetBox);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (resetBoxs.isEmpty()) {
			return null;
		} else {
			return resetBoxs;
		}
	}

	public boolean isExistSameParcel(Terminal terminal, Parcel parcel) {
		String isExistSameParcelSql = "SELECT count(package_id) FROM package where package_user_mobile_num=?"
				+ " and package_parcel_id=?"
				+ " and package_status=?"
				+ " and package_terminal_id=?" + " and package_box_id=?;";
		Object[] params = new Object[] { parcel.getPhoneNum(),
				parcel.getParcelNum(), 0, terminal.getTerminalID(),
				parcel.getBoxSeq() };
		int result = jdbcTemplate.queryForInt(isExistSameParcelSql, params);
		if (result == 0) {
			return false;
		} else {
			return true;
		}
	}

	public String isExistParcelByCheckCode(Terminal terminal, String checkCode) {
		final Parcel parcel = new Parcel();
		String IsExistParcelByCheckCodeSQL = "SELECT package_user_mobile_num FROM package "
				+ "WHERE package_status=0 AND package_terminal_id=? AND package_checkCode=?;";
		Object[] params = new Object[] { terminal.getTerminalID(), checkCode };
		jdbcTemplate.query(IsExistParcelByCheckCodeSQL, params,
				new RowCallbackHandler() {

					public void processRow(ResultSet rs) throws SQLException {
						parcel.setPhoneNum(rs
								.getString("package_user_mobile_num"));
					}
				});
		return parcel.getPhoneNum();
	}

	public ArrayList<Parcel> returnParcelInfoByPhoneNum(Terminal terminal,
			User user) {
		parcels = new ArrayList<Parcel>();
		String getParcelInfoSQl = "SELECT * FROM package where package_status=0 and package_terminal_id=? and  package_user_mobile_num=?";
		Object[] params = new Object[] { terminal.getTerminalID(),
				user.getMobileNum() };
		jdbcTemplate.query(getParcelInfoSQl, params, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				Parcel parcel = new Parcel();
				parcel.setBelong(rs.getString("package_belong"));
				parcel.setBoxSeq(rs.getString("package_box_id"));
				parcel.setParcelNum(rs.getString("package_parcel_id"));
				parcel.setPhoneNum(rs.getString("package_user_mobile_num"));
				parcel.setCheckCode(rs.getString("package_checkCode"));
				parcel.setSaveTime(rs.getTimestamp("package_store_time"));
				parcels.add(parcel);
			}
		});
		if (parcels.isEmpty()) {
			return null;
		} else {
			return parcels;
		}

	}

	/**
	 * 返回特定包裹的箱格号码
	 * 
	 * @param terminal
	 * @param parcel
	 * @return 如果没有记录，返回-1
	 */
	public int getCertainPackageBoxSeq(Terminal terminal, Parcel parcel) {
		String getCertainPackageBoxSeqSql = "SELECT package_box_id FROM package "
				+ "where package_terminal_id=? "
				+ "and package_parcel_id=? "
				+ "and package_user_mobile_num=? " + "and package_status=0;";
		Object[] params = new Object[] { terminal.getTerminalID(),
				parcel.getParcelNum(), parcel.getPhoneNum() };
		int result = -1;
		try {
			result = jdbcTemplate.queryForInt(getCertainPackageBoxSeqSql,
					params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;

	}

	public boolean updateParcelStatement(String parcelID, User user,
			Terminal terminal, String boxSeq) {
		String updateParcelStatementSQL = "UPDATE package SET package_status=1,package_get_time=? WHERE package_parcel_id=? "
				+ "and package_user_mobile_num=? and package_terminal_id=? and package_status=0 and package_box_id=?";
		Date date = new Date();
		Timestamp ts = new Timestamp(date.getTime());
		Object[] params = new Object[] { ts, parcelID, user.getMobileNum(),
				terminal.getTerminalID(), boxSeq };
		int result = jdbcTemplate.update(updateParcelStatementSQL, params);
		if (result != 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 用于在取消存件业务中更新包裹状态
	 * 
	 * @param terminal
	 * @param parcelNum
	 * @param phoneNum
	 * @param boxSeq
	 * @return
	 */
	public boolean updateParcelCancelStatement(Terminal terminal, Parcel parcel) {
		String updateParcelCancelStatementSql = "UPDATE package SET package_box_id=? WHERE package_parcel_id=? "
				+ " and package_terminal_id=? and package_user_mobile_num=? and package_status=0";
		Object[] params = new Object[] { parcel.getBoxSeq(),
				 parcel.getParcelNum(),
				terminal.getTerminalID(),parcel.getPhoneNum() };
		int result = jdbcTemplate
				.update(updateParcelCancelStatementSql, params);
		if (result != 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 
	 * @param phoneNum
	 * @param terminal
	 * @return 失败返回null
	 */
	public Map<String, Object> getCheckCodeByPhoneNumInBranch(String phoneNum,
			Terminal terminal) {
		int terminalId = -1;
		Map<String, Object> result = null;
		String getCheckCodeByPhoneNumInBranchSql = "SELECT package_checkCode,package_terminal_id FROM package where package_status=0 "
				+ " and package_user_mobile_num=? "
				+ " and package_terminal_id in (SELECT terminal_id FROM terminal where terminal_branch_id=?) "
				+ " order by package_store_time DESC limit 1 ";

		Object[] params = new Object[] { phoneNum, terminal.getBranchID() };
		try {
			result = jdbcTemplate.queryForMap(
					getCheckCodeByPhoneNumInBranchSql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return result;
	}

	/**
	 * 根据手机号返回验证码
	 * 
	 * @param phoneNum
	 * @param terminal
	 * @return
	 */
	// TODO:Unit Test
	public String getCheckCodeByPhoneNum(String phoneNum, Terminal terminal) {
		String checkCode = null;
		String getCheckCodeByPhoneNumSql = "SELECT package_checkCode FROM package "
				+ " where package_user_mobile_num=? "
				+ " and package_status=0 and package_terminal_id=? limit 1;";
		Object[] params = new Object[] { phoneNum, terminal.getTerminalID() };
		try {
			checkCode = jdbcTemplate.queryForObject(getCheckCodeByPhoneNumSql,
					params, String.class);
		} catch (Exception e) {
			// TODO: handle exception
			return null;
		}
		return checkCode;
	}
	/**
	 * 根据单号获取验证码
	 * @param parcelID
	 * @param terminal
	 * @return
	 */
	public String getCheckCodeByParcelID(String parcelID, Terminal terminal) {
		String checkCode = null;
		String getCheckCodeByPhoneNumSql = "SELECT package_checkCode FROM package "
				+ " where package_parcel_id=? "
				+ " and package_status=0 and package_terminal_id=? limit 1;";
		Object[] params = new Object[] { parcelID, terminal.getTerminalID() };
		try {
			checkCode = jdbcTemplate.queryForObject(getCheckCodeByPhoneNumSql,
					params, String.class);
		} catch (Exception e) {
			// TODO: handle exception
			return null;
		}
		return checkCode;
	}

	/**
	 * 判断包裹是否具备退件条件
	 * 
	 * @param parcel
	 * @return -1 不可退，其他可退，返回包裹号
	 */
	public Parcel withdrawParcel(Parcel parcel, Terminal terminal) {
		Date date = new Date();
		resultParcel = new Parcel();
		Timestamp ts = new Timestamp(date.getTime() - 1000 * 60 * 30);
		String withdrawParcelSql = "SELECT package_id,package_user_mobile_num FROM package WHERE package_parcel_id=? "
				+ "AND package_get_time>? "
				+ "AND package_checkCode=? "
				+ "AND package_status=1 " + "AND package_terminal_id=?";
		Object[] params = new Object[] { parcel.getParcelNum(), ts,
				parcel.getCheckCode(), terminal.getTerminalID() };
		try {
			jdbcTemplate.query(withdrawParcelSql, params,
					new RowCallbackHandler() {

						public void processRow(ResultSet rs)
								throws SQLException {
							resultParcel.setPhoneNum(rs
									.getString("package_user_mobile_num"));
							resultParcel.setId(rs.getInt("package_id"));
						}
					});
		} catch (Exception e) {
			e.printStackTrace();
		}
		resultParcel.print();
		return resultParcel;
	}

	/**
	 * 根据包裹ID返回所在箱号
	 * 
	 * @param id
	 * @return
	 */
	public int getBoxSeqByParcelID(int id) {
		String getBoxSeqByParcelIDSql = "select package_box_id from package where package_id="
				+ id;
		int boxSeq = -1;
		try {
			boxSeq = jdbcTemplate.queryForInt(getBoxSeqByParcelIDSql);
		} catch (Exception e) {
		}
		return boxSeq;
	}

	/**
	 * 处理退件，更新boxId 和 package_status 的id
	 * 
	 * @param newBoxSeq
	 * @param packageId
	 */
	public boolean dealWithdrawParcel(int newBoxSeq, int packageId,
			int oldBoxSeq) {
		String dealWithdrawParcelSql = "UPDATE package SET `package_box_id`=?, "
				+ " `package_status`='4', "
				+ " `package_comments`=? "
				+ " WHERE `package_id`=?;";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd_hh:mm:ss",
				Locale.SIMPLIFIED_CHINESE);
		String today = sdf.format(new Date());
		String comments = today + "_" + oldBoxSeq;
		Object[] params = new Object[] { newBoxSeq, comments, packageId };
		try {
			jdbcTemplate.update(dealWithdrawParcelSql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 是否存在指定快递单号和验证码的包裹
	 * 
	 * @param checkCode
	 * @param parcelNum
	 * @return 如果存在指定记录 返回true 如果不存在这样的记录 or不仅仅只有一条这样的记录 返回false
	 */
	public boolean isExtParcelByCheckCodeAndParcelNum(String checkCode,
			String parcelNum, Terminal terminal) {
		String isExtParcelByCheckCodeAndParcelNumSql = "SELECT count(*) FROM package where package_parcel_id=? "
				+ "and package_checkCode=? " + "and package_terminal_id=?;";
		Object[] params = new Object[] { parcelNum, checkCode,
				terminal.getTerminalID() };
		try {
			int result = jdbcTemplate.queryForInt(
					isExtParcelByCheckCodeAndParcelNumSql, params);
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			return false;
		}

	}

	/**
	 * 根据快递单号返回手机号码，查询packageinfo表，该表数据从中通数据后台抓去获得
	 * 
	 * @param parcelNum
	 * @return
	 */
	public String getMobileNumByParcelNum(String parcelNum) {
		String getMobileNumByParcelNumSql = "SELECT  packageInfo_consigneePhoneNum FROM packageinfo where packageInfo_packNum=?;";
		Object[] params = new Object[] { parcelNum };
		String phoneNum = null;
		try {
			phoneNum = jdbcTemplate.queryForObject(getMobileNumByParcelNumSql,
					params, String.class);
			if (phoneNum == null || phoneNum.equals("")) {
				return null;
			}
		} catch (Exception e) {
			return null;
		}
		return phoneNum;

	}

	/**
	 * 通过指定id返回Parcel实例
	 * 
	 * @param parcelId
	 * @return
	 */
	public Parcel getParcelbyId(int parcelId) {
		resultParcel = new Parcel();
		String getParcelbyIdSql = "SELECT * FROM package WHERE package_id=? ";
		Object[] params = new Object[] { parcelId };
		jdbcTemplate.query(getParcelbyIdSql, params, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setPhoneNum(rs.getString("package_user_mobile_num"));
				resultParcel.setParcelNum(rs.getString("package_parcel_id"));
				resultParcel.setCourierId(rs.getInt("package_courier_id"));
				resultParcel.setBoxSeq(rs.getString("package_box_id"));
				resultParcel.setSaveTime(rs.getTimestamp("package_store_time"));
				resultParcel.setStatus(rs.getInt("package_status"));
				resultParcel.setGetTime(rs.getTimestamp("package_get_time"));
				resultParcel.setCheckCode(rs.getString("package_checkCode"));
			}
		});
		return resultParcel;
	}

	/**
	 * 检测package表中是否有和boxStatus表冲突的数据
	 * 
	 * @param boxId
	 * @param terminalId
	 * @return
	 */
	public boolean checkBoxEmpty(int boxId, int terminalId) {
		String checkBoxEmptySql = "SELECT COUNT(package_id) FROM package "
				+ " WHERE package_status=?" + " AND package_box_id=? "
				+ "AND package_terminal_id=?";
		Object[] params = new Object[] { 0, boxId, terminalId };
		int count = jdbcTemplate.queryForInt(checkBoxEmptySql, params);
		if (count == 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 注销用户存件记录，将包裹状态修改为已取，并加入相应注释
	 * 
	 * @param parcel
	 * @return
	 */
	public boolean cancelCertainParcel(Parcel parcel) {
		String cancelCertainParcelSql = "UPDATE `package` SET `package_status`='5', `package_comments`=?, `package_get_time`=? WHERE "
				+ " `package_terminal_id`=? "
				+ " AND `package_user_mobile_num`=? "
				+ " AND `package_parcel_id`=? ";
		Object[] params = new Object[] { "用户手机号码输入错误，本条记录注销",new Timestamp(new Date().getTime()),
				parcel.getTerminalId(), parcel.getPhoneNum(),
				parcel.getParcelNum() };
		try {
			jdbcTemplate.update(cancelCertainParcelSql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;

	}

	/**
	 * 根据运单号返回未取包裹列表
	 * 
	 * @param parcelNum
	 * @param agentID
	 * @param status
	 * @return
	 */
	public ArrayList<UnTakenParcel> queryUnTakenParcelByParcelNum(
			String parcelNum, int agentID,int status) {
		unTakenParcels = new ArrayList<UnTakenParcel>();
		String sql = null;
		if(status==-1){
			sql = "SELECT * FROM package p,terminal t,branch b,courier c where p.package_parcel_id=? and p.package_status<>? and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc;";
		}else{
			sql = "SELECT * FROM package p,terminal t,branch b,courier c where p.package_parcel_id=? and p.package_status=? and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc;";
		}
/*		String sql = "SELECT p.package_box_id,p.package_user_mobile_num,p.package_belong,c.courier_mobileNum,c.courier_name,p.package_checkCode,p.package_parcel_id,p.package_store_time FROM package p,terminal t,branch b,courier c where p.package_parcel_id=? and p.package_status=0 and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc;";*/		
		Object[] params = new Object[] { parcelNum,status, agentID };
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				UnTakenParcel unTakenParcel = new UnTakenParcel();
				unTakenParcel.setBoxNum(rs.getInt("package_box_id"));
				unTakenParcel.setCompany(rs.getString("package_belong"));
				unTakenParcel.setCourierMobile(rs
						.getString("courier_mobileNum"));
				unTakenParcel.setCourierName(rs.getString("courier_name"));
				unTakenParcel.setUserMobileNum(rs
						.getString("package_user_mobile_num"));
				unTakenParcel.setGetCode(rs.getString("package_checkCode"));
				unTakenParcel.setParcelNum(rs.getString("package_parcel_id"));
				unTakenParcel.setSaveTime(rs.getTimestamp("package_store_time")
						.getTime() / 1000);
				unTakenParcels.add(unTakenParcel);
			}
		});
		return unTakenParcels;
	}

	/**
	 * 根据用户手机号码返回未取包裹列表
	 * 
	 * @param phoneNum
	 * @param role
	 * @param agentID
	 * @return
	 */
	public ArrayList<UnTakenParcel> queryUnTakenParcelByPhoneNum(
			String phoneNum, int role, int agentID,int status) {
		unTakenParcels = new ArrayList<UnTakenParcel>();
		String sql = null;
		if (role == 2) {
			if(status==-1){
				sql = "SELECT * FROM package p,terminal t,branch b,courier c where c.courier_mobileNum=? and p.package_status<>? and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc;";
			}else{
				sql = "SELECT * FROM package p,terminal t,branch b,courier c where c.courier_mobileNum=? and p.package_status=? and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc;";
			}
		} else {
			if(status==-1){
				sql = "SELECT * FROM package p,terminal t,branch b,courier c where p.package_user_mobile_num=? and p.package_status<>? and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc;";
			}else{
				sql = "SELECT * FROM package p,terminal t,branch b,courier c where p.package_user_mobile_num=? and p.package_status=? and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc;";
			}
		}
		Object[] params = new Object[] {phoneNum, status,agentID};
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				UnTakenParcel unTakenParcel = new UnTakenParcel();
				unTakenParcel.setBoxNum(rs.getInt("package_box_id"));
				unTakenParcel.setCompany(rs.getString("package_belong"));
				unTakenParcel.setCourierMobile(rs
						.getString("courier_mobileNum"));
				unTakenParcel.setCourierName(rs.getString("courier_name"));
				unTakenParcel.setUserMobileNum(rs
						.getString("package_user_mobile_num"));
				unTakenParcel.setGetCode(rs.getString("package_checkCode"));
				unTakenParcel.setParcelNum(rs.getString("package_parcel_id"));
				unTakenParcel.setSaveTime(rs.getTimestamp("package_store_time")
						.getTime() / 1000);
				unTakenParcel.setParcelAddr(rs.getString("branch_shortAddr")
						+ rs.getString("terminal_name"));
				unTakenParcels.add(unTakenParcel);
			}
		});
		return unTakenParcels;
	}

	/**
	 * 通过手机号码查询历史包裹
	 * 
	 * @param phoneNum
	 * @param role
	 * @param agentID
	 * @param startNum
	 * @param endNum
	 * @return
	 */
	public ArrayList<HistoryParcel> queryHistoryParcelByPhoneNum(
			String phoneNum, int role, int agentID, int startNum, int endNum) {
		historyParcels = new ArrayList<HistoryParcel>();
		String sql = null;
		if (role == 2) {
			sql = "SELECT b.branch_shortAddr,t.terminal_name,p.package_box_id,p.package_user_mobile_num,p.package_belong,c.courier_mobileNum,c.courier_name,p.package_checkCode,p.package_parcel_id,p.package_store_time,p.package_get_time FROM package p,terminal t,branch b,courier c where c.courier_mobileNum=? and p.package_status<>0 and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc limit "
					+ startNum + "," + endNum + ";";
		} else {
			sql = "SELECT b.branch_shortAddr,t.terminal_name,p.package_box_id,p.package_user_mobile_num,p.package_belong,c.courier_mobileNum,c.courier_name,p.package_checkCode,p.package_parcel_id,p.package_store_time,p.package_get_time FROM package p,terminal t,branch b,courier c where p.package_user_mobile_num=? and p.package_status<>0 and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id order by p.package_store_time desc limit "
					+ startNum + "," + endNum + ";";
		}
		Object[] params = new Object[] { phoneNum, agentID };
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				HistoryParcel historyParcel = new HistoryParcel();
				historyParcel.setBoxNum(rs.getInt("package_box_id"));
				historyParcel.setCompany(rs.getString("package_belong"));
				historyParcel.setCourierMobile(rs
						.getString("courier_mobileNum"));
				historyParcel.setCourierName(rs.getString("courier_name"));
				historyParcel.setGetCode(rs.getString("package_checkCode"));
				historyParcel.setParcelAddr(rs.getString("branch_shortAddr")
						+ rs.getString("terminal_name"));
				if (rs.getTimestamp("package_get_time") == null) {
					historyParcel.setGetTime(0);
				} else {
					historyParcel.setGetTime(rs
							.getTimestamp("package_get_time").getTime() / 1000);
				}
				historyParcel.setParcelNum(rs.getString("package_parcel_id"));
				historyParcel.setSaveTime(rs.getTimestamp("package_store_time")
						.getTime() / 1000);
				historyParcel.setUserMobileNum(rs
						.getString("package_user_mobile_num"));
				historyParcels.add(historyParcel);
			}
		});
		return historyParcels;
	}

	/**
	 * 根据单号查询唯一包裹
	 * 
	 * @param parcelNum
	 * @param agentID
	 * @return
	 */
	public Parcel queryByParcelNum(String parcelNum, String checkCode,
			int agentID) {
		String sql = "SELECT p.package_id,p.package_terminal_id,p.package_box_id FROM package p,terminal t,branch b where p.package_parcel_id=? and p.package_checkCode=? and p.package_status=0 and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id limit 1;";
		Object[] params = new Object[] { parcelNum, checkCode, agentID };
		resultParcel = new Parcel();
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setBoxSeq(String.valueOf(rs
						.getInt("package_box_id")));
			}
		});
		if (resultParcel.getId() == 0) {
			return null;
		} else {
			return resultParcel;
		}
	}

	/**
	 * 根据单号返回唯一包裹
	 * 
	 * @param parcelNum
	 * @return
	 */
	public Parcel queryByParcelNum(String parcelNum) {
		String sql = "SELECT p.package_checkCode,p.package_parcel_id,p.package_user_mobile_num,p.package_id,p.package_terminal_id,p.package_box_id FROM package p where p.package_parcel_id=? and p.package_status=0 limit 1;";
		Object[] params = new Object[] { parcelNum };
		resultParcel = new Parcel();
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setBoxSeq(String.valueOf(rs
						.getInt("package_box_id")));
				resultParcel.setCheckCode(rs.getString("package_checkCode"));
				resultParcel.setParcelNum(rs.getString("package_parcel_id"));
				resultParcel.setPhoneNum(rs
						.getString("package_user_mobile_num"));
			}
		});
		if (resultParcel.getId() == 0) {
			return null;
		} else {
			return resultParcel;
		}
	}
	/**
	 * 条件查询包裹信息
	 * @param terminalID
	 * @param parcelNum
	 * @param phoneNum
	 * @return
	 * by zgy
	 */
	public Parcel queryParcel(int terminalID,String parcelNum,String phoneNum,int status) {
		String sql = "SELECT * FROM package p where p.package_terminal_id=? and p.package_parcel_id=? and p.package_user_mobile_num=? and p.package_status=? order by p.package_store_time desc limit 1;";
		Object[] params = new Object[] { terminalID,parcelNum,phoneNum,status };
		resultParcel = new Parcel();
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setBoxSeq(String.valueOf(rs.getInt("package_box_id")));
				resultParcel.setCheckCode(rs.getString("package_checkCode"));
				resultParcel.setParcelNum(rs.getString("package_parcel_id"));
				resultParcel.setPhoneNum(rs.getString("package_user_mobile_num"));
				resultParcel.setGetTime(rs.getTimestamp("package_get_time"));
				resultParcel.setStatus(rs.getInt("package_status"));
				resultParcel.setBelong(rs.getString("package_belong"));
				resultParcel.setSaveTime(rs.getTimestamp("package_store_time"));
			}
		});
		if (resultParcel.getId() == 0) {
			return null;
		} else {
			return resultParcel;
		}
	}
	
	public Parcel queryLatestParcel(String parcelNum) {
		String sql = "SELECT p.package_status,p.package_checkCode,p.package_parcel_id,p.package_user_mobile_num,p.package_id,p.package_terminal_id,p.package_box_id FROM package p where p.package_parcel_id=? order by package_store_time desc limit 1;";
		Object[] params = new Object[] { parcelNum };
		resultParcel = new Parcel();
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setBoxSeq(String.valueOf(rs
						.getInt("package_box_id")));
				resultParcel.setCheckCode(rs.getString("package_checkCode"));
				resultParcel.setParcelNum(rs.getString("package_parcel_id"));
				resultParcel.setPhoneNum(rs
						.getString("package_user_mobile_num"));
				resultParcel.setStatus(rs.getInt("package_status"));
			}
		});
		if (resultParcel.getId() == 0) {
			return null;
		} else {
			return resultParcel;
		}
	}
	
	public Parcel latestParcel(int terminalID,int boxNum) {
		String sql = "SELECT p.package_status,p.package_checkCode,p.package_parcel_id,p.package_user_mobile_num,p.package_id,p.package_terminal_id,p.package_box_id FROM package p where p.package_terminal_id=? and p.package_box_id=? order by p.package_store_time desc limit 1;";
		Object[] params = new Object[] { terminalID, boxNum};
		resultParcel = new Parcel();
		jdbcTemplate.query(sql, params, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				resultParcel.setId(rs.getInt("package_id"));
				resultParcel.setTerminalId(rs.getInt("package_terminal_id"));
				resultParcel.setBoxSeq(String.valueOf(rs
						.getInt("package_box_id")));
				resultParcel.setCheckCode(rs.getString("package_checkCode"));
				resultParcel.setParcelNum(rs.getString("package_parcel_id"));
				resultParcel.setPhoneNum(rs
						.getString("package_user_mobile_num"));
				resultParcel.setStatus(rs.getInt("package_status"));
			}
		});
		if (resultParcel.getId() == 0) {
			return null;
		} else {
			return resultParcel;
		}
	}

	/**
	 * 更新包裹状态
	 * 
	 * @param parcelID
	 * @param status
	 * @return
	 */
	public boolean updateParcelStatus(int parcelID, int status) {
		String sql = "UPDATE package SET package_status=? WHERE package_id=? ";
		Object[] params = new Object[] { status, parcelID };
		int result = jdbcTemplate.update(sql, params);
		if (result != 0) {
			return true;
		} else {
			return false;
		}
	}
	
	public boolean updateParcelFlag(int parcelID, int flag) {
		String sql = "UPDATE package SET package_status=? WHERE package_id=? ";
		Object[] params = new Object[] { flag, parcelID };
		int result = jdbcTemplate.update(sql, params);
		if (result != 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 根据手机号码返回历史包裹
	 * 
	 * @param phoneNum
	 * @param role
	 * @param agentID
	 * @return
	 */
	public int totalHistoryParcels(String phoneNum, int role, int agentID) {
		String sql = null;
		if (role == 2) {
			sql = "SELECT count(*) FROM package p,terminal t,branch b,courier c where c.courier_mobileNum=? and p.package_status<>0 and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id;";
		} else {
			sql = "SELECT count(*) FROM package p,terminal t,branch b,courier c where p.package_user_mobile_num=? and p.package_status<>0 and b.branch_agent_id=? and t.terminal_branch_id=b.branch_id and p.package_terminal_id=t.terminal_id and p.package_courier_id=c.courier_id;";
		}
		Object[] params = new Object[] { phoneNum, agentID };
		try {
			return jdbcTemplate.queryForInt(sql, params);
		} catch (Exception e) {
			return 0;
		}
	}

	public boolean updateFlagByOrderNo(String orderNo, int flag) {
		String sql = "update `package` set `package_flag`=? where `package_parcel_id`=?;";
		Object[] params = new Object[] { flag, orderNo };
		try {
			jdbcTemplate.update(sql, params);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

}
